﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Xml;
using System.IO;
namespace FlexControls
{
    public partial class fcConnection : Form
    {
        
        public ADODB.Connection conn;
        public ADODB.Connection flexConn;
        public string[] strSQL;
        public fcConnection()
        {
            InitializeComponent();
        }


        public string strServer = "";
        public string strUser = "";
        public string strPassword = "";
        public string strDatabase = "master";
        public const string MASTER_DB_NAME = "master";
        public const string SQL_GETDATABASE_NAME = "SELECT [Name] FROM [sysdatabases]";
        public const int DATABSE_COLUMN_NAME = 0;

        public void ReadXML(string strXMLName, ref string[] strXML)
        {
            try
            {
                
                
                string strpath = Application.StartupPath;
                XmlDataDocument reader = new XmlDataDocument();
                XmlNodeList xmlnode;
                FileStream fs = new FileStream(Application.StartupPath + "\\Xml\\" + "DatabaseInfo.xml", FileMode.Open, FileAccess.Read);
                reader.Load(fs);
                fs.Flush();
                fs.Close();
                xmlnode = reader.GetElementsByTagName("information");
                for (int i = 0; i < xmlnode[0].ChildNodes.Count; i++)
                {
                    switch (xmlnode[0].ChildNodes.Item(i).Name)
                    {
                        case "Server":
                            strSQL [flexControls.ELE_SERVER] = Convert.ToString(xmlnode[0].ChildNodes.Item(i).InnerText.Trim());
                            break;
                        case "User":
                            strSQL[flexControls.ELE_USER] = Convert.ToString(xmlnode[0].ChildNodes.Item(i).InnerText.Trim());
                            break;
                        case "Password":
                            strSQL[flexControls.ELE_PASSWORD] = Convert.ToString(xmlnode[0].ChildNodes.Item(i).InnerText.Trim());
                            break;
                        case "Database":
                            strSQL[flexControls.ELE_DATABASE] = Convert.ToString(xmlnode[0].ChildNodes.Item(i).InnerText.Trim());
                            break;
                    }

                }
                strXML = strSQL;
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
            }
        }
        public bool ConnectDatabase()
        {
            try
            {

                conn = new ADODB.Connection();
                conn.ConnectionString = "Provider=SQLOLEDB.1;Password=" + strPassword + ";Persist Security Info=True;User ID=" + strUser + ";Initial Catalog=" + strDatabase + "  ;Data Source=" + strServer + "";
               // conn.ConnectionString = "Data Source=DUONGKYHIEU-PC;Initial Catalog=QLAU;Integrated Security=True";
                conn.Open(conn.ConnectionString, strUser, strPassword, -1);
                if (conn.State == 1)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
                return false;
            }
        }
        public Hashtable DetectIP()
        {
            try
            {
                int countIP = 2;
                Hashtable IPTable = new Hashtable();
                System.Net.IPHostEntry heserver;
                heserver = System.Net.Dns.GetHostEntry(System.Net.Dns.GetHostName());
                IPTable.Add(1, System.Net.Dns.GetHostName());
                foreach (System.Net.IPAddress ipe in heserver.AddressList)
                {

                    IPTable.Add(countIP, ipe.ToString());
                    countIP = countIP + 1;
                }

                return IPTable;
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
                return null;
            }

        }
        public void CallConnectSQL()
        {
            try
            {
                string[] strXML = new string[10];
                ReadXML(flexControls.XMLFILENAME, ref strXML);

                strServer = strXML[flexControls.ELE_SERVER];
                txtUser.Text = strUser = strXML[flexControls.ELE_USER];
                txtPassword.Text = strPassword = strXML[flexControls.ELE_PASSWORD];
                strDatabase = strXML[flexControls.ELE_DATABASE];
                if (strDatabase != "" && strDatabase != null)
                {
                    ConnectDatabase();
                }

                conn = new ADODB.Connection();
                if (conn.State == 1 && conn!= null)
                {

                   flexConn= new ADODB.Connection();
                   flexConn = conn;
                }
                else
                {
                    conn = new ADODB.Connection();

                    this.ResizeRedraw = false;
                    //Variable for IP detect
                    Hashtable IPTable = new Hashtable();
                    //Get IP
                    IPTable = DetectIP();
                    //Add IP to server combobox
                    for (int i = 1; i < IPTable.Count; i++)
                    {
                        if (this.cmbServers.Text == "")
                        {
                            this.cmbServers.Text = IPTable[1].ToString();
                            this.cmbServers.Items.Add(IPTable[i]);
                        }
                    }
                    this.cmbServers.Items.Add(System.Net.IPAddress.Loopback.ToString());
                    this.Show();
                }
            }
            catch (Exception ex)
            {

                MessageBox.Show(Convert.ToString(ex));
            }
        }
        public void WriteXMLDatabase(string Server, string User, string Password, string Database)
        {
            try
            {
                string strpath = Application.StartupPath;
                System.Xml.XmlTextReader reader = new System.Xml.XmlTextReader(Application.StartupPath + "\\Xml\\" + "DatabaseInfo.xml");
                System.Xml.XmlTextWriter writter = new System.Xml.XmlTextWriter(Application.StartupPath + "\\Xml\\" + "DatabaseInfo.xml", System.Text.UTF8Encoding.Unicode);
                writter.WriteStartDocument();
                writter.WriteStartElement("config");
                writter.WriteStartElement("information");

                writter.WriteStartElement("Server");
                writter.WriteString(Server);
                writter.WriteEndElement();

                writter.WriteStartElement("User");
                writter.WriteString(User);
                writter.WriteEndElement();

                writter.WriteStartElement("Password");
                writter.WriteString(Password);
                writter.WriteEndElement();

                writter.WriteStartElement("Database");
                writter.WriteString(Database);
                writter.WriteEndElement();

                writter.WriteEndElement();
                writter.WriteEndElement();
                writter.WriteEndDocument();
                writter.Flush();
                writter.Close();
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
            }

        }

        private void GetDatabase()
        {
            try
            {
                //'Check textbox
                //'If textbox not empty, connect to master table to get database name
                //'Connect to Sysdatabases to get Database name
                //'Add dabase name to Combobox database
                if (conn.State == 1)
                {
                    this.cmbDatabases.Items.Clear();
                    ADODB.Recordset rs = new ADODB.Recordset();
                    string strSQL = SQL_GETDATABASE_NAME;
                    rs.Open(strSQL, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, -1);
                    if (rs.RecordCount > 0)
                    {

                        this.cmbDatabases.Text = rs.Fields[DATABSE_COLUMN_NAME].Value.ToString();
                        for (int i = 0; i < rs.RecordCount; i++)
                        {

                            this.cmbDatabases.Items.Add(rs.Fields[DATABSE_COLUMN_NAME].Value);
                            rs.MoveNext();
                        }

                    }
                }

            }
            catch (Exception ex)
            {
                ex.Message.ToString();
            }
        }
        private void fcConnection_Load(object sender, EventArgs e)
        {

            try
            {
                CallConnectSQL();
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
            }
        }

        private void butGetDatabases_Click(object sender, EventArgs e)
        {
            try
            {
                //'Check textbox
                //'If textbox not empty, connect to master table to get database name
                if (this.cmbServers.Text != "" && this.txtUser.Text != "")
                {
                    strServer = this.cmbServers.Text;
                    strDatabase = MASTER_DB_NAME;
                    strUser = this.txtUser.Text;
                    strPassword = this.txtPassword.Text;
                    //'Connect to Sysdatabases to get Database name
                    //'Add dabase name to Combobox database
                    ConnectDatabase();

                    if (conn.State == 1)
                    {
                        GetDatabase();
                        butConnect.Enabled = true;
                    }
                    else
                    {
                        butConnect.Enabled = false;
                    }
                }

                else
                {

                    MessageBox.Show("Please insert infomation to connect database");
                }
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
            }
        }

        private void butConnect_Click(object sender, EventArgs e)
        {
            try
            {
                if (ckbRemember.Checked == true)
                {
                    WriteXMLDatabase(this.cmbServers.Text, this.txtUser.Text, this.txtPassword.Text, this.cmbDatabases.Text);
                }

                if (this.cmbDatabases.Text != "")
                {
                    strDatabase = this.cmbDatabases.Text;
                    ConnectDatabase();
                    if (conn.State == 1)
                    {
                        flexConn = new ADODB.Connection();
                        flexConn = conn;
                        this.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(Convert.ToString(ex));
            }
        }
    }
}
